﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using RESTServices.Dominio;
using System.Data.SqlClient;

namespace RESTServices.Persistencia
{
    public class ReservaDAO
    {

        public List<ReservaAdic> ListarTodos(int codigo)
        {

            List<ReservaAdic> resEncontradas = new List<ReservaAdic>();
            ReservaAdic res;
            string sql = "SELECT r.id_reserva, u.nombre, s.nombre as sala,r.dia, r.turno, r.observacion,r.estado FROM reserva r inner join usuario u on r.id_usuario=u.id_usuario inner join sala s on r.id_sala=s.id_sala where r.id_usuario=@cod order by r.id_reserva";
            using (SqlConnection con = new SqlConnection(Conexion.ObtenerCadena))
            {
                con.Open();
                using (SqlCommand com = new SqlCommand(sql, con))
                {
                    com.Parameters.Add(new SqlParameter("@cod",codigo));
                    using (SqlDataReader resultado = com.ExecuteReader())
                    {
                        while (resultado.Read())
                        {
                            res = new ReservaAdic()
                            {
                                id_reserva = (int)resultado["id_reserva"],
                                usuario = (string)resultado["nombre"],
                                sala = (string)resultado["sala"],
                                dia = (string)resultado["dia"],
                                turno = (string)resultado["turno"],
                                observacion = (string)resultado["observacion"],
                                estado = (string)resultado["estado"]
                            };
                            resEncontradas.Add(res);
                        }
                    }
                }
            }
            return resEncontradas;
        }

        public ReservaAdic Crear(Reserva resACrear)
        {

            string sql2 = "select MAX (id_reserva)+1 as id_reserva from reserva";
            int xId;
            using (SqlConnection con = new SqlConnection(Conexion.ObtenerCadena))
            {

                con.Open();
                using (SqlCommand com = new SqlCommand(sql2, con))
                {
                    xId = Convert.ToInt32(com.ExecuteScalar());
                }
            }

            ReservaAdic resCreada = new ReservaAdic();
            string sql = "INSERT INTO reserva VALUES(@cod,@usu,@sal,@dia,@tur,@obs,@est)";
            //using (SqlConnection con = new SqlConnection(Conexion.ObtenerCadena))
            using (SqlConnection con = new SqlConnection(Conexion.ObtenerCadena))
            {
                con.Open();
                using (SqlCommand com = new SqlCommand(sql, con))
                {
                    com.Parameters.Add(new SqlParameter("@cod", xId));
                    com.Parameters.Add(new SqlParameter("@usu", resACrear.id_usuario));
                    com.Parameters.Add(new SqlParameter("@sal", resACrear.id_sala));
                    com.Parameters.Add(new SqlParameter("@dia", resACrear.dia));
                    com.Parameters.Add(new SqlParameter("@tur", resACrear.turno));
                    com.Parameters.Add(new SqlParameter("@obs", resACrear.observacion));
                    com.Parameters.Add(new SqlParameter("@est", resACrear.estado));
                    com.ExecuteNonQuery();
                }
            }
            int cod = resCreada.id_reserva;
            resCreada = Obtener(resCreada.id_reserva);
            
            return resCreada;
        }

        public ReservaAdic Obtener(int id)
        {
            ReservaAdic resEncontrada = null;
            string sql = "SELECT r.id_reserva, u.nombre, s.nombre as sala,r.dia, r.turno, r.observacion,r.estado FROM reserva r inner join usuario u on r.id_usuario=u.id_usuario inner join sala s on r.id_sala=s.id_sala where r.id_reserva=@cod order by r.id_reserva";
            using (SqlConnection con = new SqlConnection(Conexion.ObtenerCadena))
            {
                con.Open();
                using (SqlCommand com = new SqlCommand(sql, con))
                {
                    com.Parameters.Add(new SqlParameter("@cod", id));
                    using (SqlDataReader resultado = com.ExecuteReader())
                    {
                        if (resultado.Read())
                        {
                            resEncontrada = new ReservaAdic()
                            {
                                id_reserva = (int)resultado["id_reserva"],
                                usuario = (string)resultado["nombre"],
                                sala = (string)resultado["sala"],
                                dia = (string)resultado["dia"],
                                turno = (string)resultado["turno"],
                                observacion = (string)resultado["observacion"],
                                estado = (string)resultado["estado"]
                            };
                        }
                    }
                }

            }
            return resEncontrada;
        }

        public void Eliminar(int id)
        {

            string sql = "DELETE from reserva where id_reserva=@cod";
            using (SqlConnection con = new SqlConnection(Conexion.ObtenerCadena))
            {
                con.Open();
                using (SqlCommand com = new SqlCommand(sql, con))
                {
                    com.Parameters.Add(new SqlParameter("@cod", id));
                    com.ExecuteNonQuery();
                }
            }

        }

        public ReservaAdic Modificar(Reserva resAModificar)
        {
            ReservaAdic resModificar = new ReservaAdic();
            string sql = "UPDATE reserva SET id_sala=@sal,dia=@dia,turno=@tur,observacion=@obs where id_reserva=@cod";

            using (SqlConnection con = new SqlConnection(Conexion.ObtenerCadena))
            {
                con.Open();
                using (SqlCommand com = new SqlCommand(sql, con))
                {


                    //com.Parameters.Add(new SqlParameter("@usu", resAModificar.id_usuario));
                    com.Parameters.Add(new SqlParameter("@sal", resAModificar.id_sala));
                    com.Parameters.Add(new SqlParameter("@dia", resAModificar.dia));
                    com.Parameters.Add(new SqlParameter("@tur", resAModificar.turno));
                    com.Parameters.Add(new SqlParameter("@obs", resAModificar.observacion));
                   // com.Parameters.Add(new SqlParameter("@est", resAModificar.estado));
                    com.Parameters.Add(new SqlParameter("@cod", resAModificar.id_reserva));
                    com.ExecuteNonQuery();

                }
            }
            resModificar = Obtener(resAModificar.id_reserva);
            return resModificar;
        }


        public Reserva validar(Reserva resVal)
        {
            Reserva resEncontrada = null;
          
            string sql = "select * from reserva where id_sala=@cod and dia = @dia and turno = @tur";
            using (SqlConnection con = new SqlConnection(Conexion.ObtenerCadena))
            {
                con.Open();
                using (SqlCommand com = new SqlCommand(sql, con))
                {
                    com.Parameters.Add(new SqlParameter("@cod", resVal.id_sala));
                    com.Parameters.Add(new SqlParameter("@dia", resVal.dia));
                    com.Parameters.Add(new SqlParameter("@turno", resVal.turno));
                   
                    using (SqlDataReader resultado = com.ExecuteReader())
                    {
                        if (resultado.Read())
                        {
                            resEncontrada = new Reserva()
                            {
                                id_reserva = (int)resultado["id_reserva"],
                                id_usuario = (int)resultado["id_usuario"],
                                id_sala = (int)resultado["id_sala"],
                                dia = (string)resultado["dia"],
                                turno = (string)resultado["turno"],
                                observacion = (string)resultado["observacion"],
                                estado = (string)resultado["estado"]
                            };
                        }
                    }
                }

            }
            return resEncontrada;
        }


        public List<Reserva> Listar(int codigo)
        {

            List<Reserva> resEncontradas = new List<Reserva>();
            Reserva res;
            string sql = "SELECT * from reserva where id_usuario=@cod";
            using (SqlConnection con = new SqlConnection(Conexion.ObtenerCadena))
            {
                con.Open();
                using (SqlCommand com = new SqlCommand(sql, con))
                {
                    com.Parameters.Add(new SqlParameter("@cod", codigo));
                    using (SqlDataReader resultado = com.ExecuteReader())
                    {
                        while (resultado.Read())
                        {
                            res = new Reserva()
                            {
                                id_reserva = (int)resultado["id_reserva"],
                                id_usuario = (int)resultado["id_usuario"],
                                id_sala = (int)resultado["id_sala"],
                                dia = (string)resultado["dia"],
                                turno = (string)resultado["turno"],
                                observacion = (string)resultado["observacion"],
                                estado = (string)resultado["estado"]
                            };
                            resEncontradas.Add(res);
                        }
                    }
                }
            }
            return resEncontradas;
        }
    }
}